There is a bunch of discussion on what is the best solution. If you EncryptSensitiveWithPassword a Package Password has to be set and used every time the package is run.

You can have package level configurations and handle it there so that the password isn't saved in the package, but in a configuration.

I see, hmm -- I like the sound of keeping it on a config. I'll look more into thos -- thanks for you reply!

I think I can do one better.
First of all I do import the package with the "Rely on server.... blah... blah... blah...". However I import and install as an local Administrator, but the SQL Server Agent is set to run as a User, not and Administrator. As a result I execute the package which come out with the message posted below. The "funny" part is - it seems that after all the package does execute susccessfully and does everything it is supposed to do (at a glance - I'm still digging). So this message seems to be nothing but a big annoyance, which will not look good in front of the management. Any thoughts?


Executed as user: LOCAL\SSISuser.
Microsoft (R) SQL Server Execute Package Utility Version 10.0.1600.22 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 11:10:00 PM
Error: 2009-02-16 23:10:03.13
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 11:10:00 PM
Finished: 11:10:36 PM
Elapsed: 36.271 seconds.
The package executed successfully.
The step succeeded.

Make sure that the lastest service pack is installed for SSIS. As of today KB955706

http://support.microsoft.com/kb/918222/

http://support.microsoft.com/kb/913089/

http://support.microsoft.com/kb/KB955706

Let's Say your package name is MyPackage
In Visual Studio Go to Control Flow Tab.
Righ Click on an empty areainside the window not clicking "Data Flow Component". pop up menu click the the propertiesto get to the propertieswindow of MyPackage package.

Under the Security Area-> You will see

ProtectionLevel -- Change that to EncryptSensitiveWithPassword
PackagePassword --enter password-> temp

This should do the trick however to be sure:
Below you will connection managers:
Database Connections (if more than one preform on all)
Double Click your connection to get the property pages. Click "ALL" under the Connection Link on Left Side. Scroll Down to Security Area.
Provide the followings:
Password (for the sql userid being used)
Persist Security Info = True

----------------------------------------------------------------------
Save the Package and connect to SQL Integration Srvices in SQL Manager (To Server e.g;DBServer (Integration Services)

Stored Packages ->MSDB --> Right Click --> choose Import Package

in the property dialog box
Package Location : File System
Package Path -- Choose the location of your dtsxfile.(MyPackage.dtsx)

Leave everything default.

Click OK.

Dialog box will appearasking for the Package Password
Provide the password-> temp

You have successfully imported the package called MyPackage.

In order to create a job.

In the job Step->

Type: SQL Server Integration Services Package
In the General Tab:
Package Source : SSIS Package Store
Server: DBServer (Where westored our package above)

Click the button for the package: Choose your package (MyPackage)

Click OK :

It will ask the package password again : temp


Package has successfully been loaded to Job Step. Now you can schedule and do a test run on the job.

Thanks for the patience of reading for those who are expert.

- Azhar

Hi dwith,

I`m trying to save packages in SSIS server with EncryptSensitiveWithUserKey, but when I ran a job, it failed, but what you saysolves the problem. Thank you.

Would really appreciate if you could you propose any solution to the following scenario?

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/6679cb1f-1210-417e-89ea-53d840000b10

dwith..... you are a CAMP mate... thx... it worked for me great...... :) thanks for your help........

Dwith,

 

Thanks so much for your post.  Your suggestion ended a 3 hour head scratching session for me!!!

This is working for me.... Thanks Azhar and you are awsome...

Azhar had it right. Thanks a bunch. I only made one small change to his process.

When I import the DTSX package into SQL Server in step 2, I did not leave everything default. The last option in the Import Package dialog box is "Protection Level". I changed this option to "Rely on server storage and roles for protection level".

I think that if your environment is based on Windows authentication, this is a better option anyway. And even if it's not, it also gets rid of the pesky password prompt that pops up any time you try to make changes to a job that includes the package as a step.

The password prompt might not be an issue for the creator of the package (since he/she knows the password). But any developer that comes after you might find this to be a serious roadblock...

...unless you documented everything meticulously...

And I know you did!

-T

I want to change the protection level of packages to "DontSaveSensitive" so I added these packages in to a new Integration Service Project using "SQL Server Business Intelligence Development Studio" and saved the Protection Level Property to "DontSaveSensitive" for all the packages.

But

Again when I am trying to add the same packages in to any other Integration Service Project it still shows protection level
as "EncryptSensitiveWithUserKey" which is causing the below error while trying to Load or Promote the packge into SQL server from command promopt.

Error 1 Error loading HACAppUserScopePackage 1.dtsx: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.   C:\Documents and Settings\dkanabar\My Documents\Visual Studio 2005\Projects\Integration Services Project3\Integration Services Project3\HACAppUserScopePackage 1.dtsx 1 1

Please help me to solve the problem. I want to set the Protection Level property of Package.

 

If anyone is looking for this blog post on the Cubido website, it looks like they reorganized their blog storage a bit.  Try this link instead:

http://www.cubido.at/blogs/Lists/Posts/Post.aspx?ID=1402

-km 

Thanks KM for the 'current' link; however, I wanted to thank Azhar for his input.  I had all sorts of issues with it executing in a job until I saw his step by step post.  Thanks all of you.  This is still relavant after all these years!  Heidi

the link no longer works, including the updated one.

 

Could someone just post the answer here instead of the link to that site which keeps changing.

 

Thanks!

One extra piece of information which took me ages to figure out: Use windows authentication when defining the connection in Connection Manager if using 'DonSaveSensitive'.

I had specified a SQL login in the connection strings and had the problems listed above - the package ran fine in SSIS, but refused to work when run by the SQL Agent. I changed the ProtectionLevel to 'DontSaveSensitive' - but then I had the new problem of the package not containing the passwords to use the specified connection string and so the package failed since it was unable to access the DBs.

The solution (obvious in hindsight) is to not use SQl Server authentication when specifying the Connection in the first place. By switching the Connection to use Windows auth, there was no password, and hence no sensitive information to lose by changing the Protection Level. When the sql agent tries to run the package, it uses it's own (domain account) credentials and so long as it has rights access the required data, the package finally works!

(For what it is worth, I also had issues with the package being run by the 64bit runtime and being unable to access a data source of a specific type, but I found the solution to this online (tick use 32bit runtime in /JobstepProperties/Execution Options) - I couldn't find anything about using Windows Auth with 'DontSaveSensitive').

• Proposed as answer by SqlSam Monday, March 04, 2013 6:43 PM

Hi,

I had the same problem as everyone here seems to be having. Im sitting on SQL server 2008 r2.

Tried the most things people here has posted. But nothing seemed to work. When I finally imported the package from the SSIS project into SQL server agent and there, and only there, changed protection level to "rely on server storage and roles for access control" like the following picture shows:



After this everything worked without changing anything in the project file.

Thanks dwith... now my package is running fine.

Thanks so much dwith,

I also had a similar problem as you did & I almost couldn't find why a job that runs two of my packages on SSIS 2005 that retrieve data from Oracle server & export them into csv files cannot run successfully. Using Visual Studio 2005, I tried to enter user name & password that can access to Oracle database in my packages & saved it. But for some reasons, it didn't save at all even though the box "Save password" was selected or marked. If I run these packages manually from SSIS, they can run very well without any problem.

Now following your instructions, then my issue was solved & I'm really happy with it. The job ran smoothly.

Thanks again dwith,

Edward

The link doesn't work. Anyway thanks for posting!

Hi All,

I too got the similar error:

"Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available"

I had XML configuration at package level to get Connection sting and Variable values. I created a new SSIS solution and added my existing package and modified few things and then next day I got above error.

After going through the thread, I checked the "ProtectionLevel" of my package and found it is "SaveSensitivewithuserkey" then I tried to change it at package level the It asked to change it first at Project level and the at package level.

I did this cahnge and next day there was no error or warning.

Hi friend

There are many days I'm having authentication problems with the FTP server when I run the SSIS application developed using the JOB in the SQL Server.

Thank you very much!

Robinson - Brazil